﻿namespace AnyOffice.DAL
{
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class MyTableDao
    {
        public DataTable SelectAll(SqlConnection connn)
        {
            string commandText = "SELECT [ModuleId], [FuncId], [ModuleName], [ModulePos], [ModuleNo], [ModuleControl], [ViewType] FROM [dbo].[MyTable] ORDER BY [ModulePos] ASC, [ModuleNo] ASC";
            DataSet set = SqlHelper.ExecuteDataset(connn, CommandType.Text, commandText);
            set.Tables[0].TableName = "MyTable";
            return set.Tables[0];
        }

        public DataTable SelectByModuleId(SqlConnection connn, int moduleId)
        {
            string commandText = "SELECT [ModuleId], [FuncId], [ModuleName], [ModulePos], [ModuleNo], [ModuleControl], [ViewType] FROM [dbo].[MyTable] WHERE [ModuleId] = @ModuleId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ModuleId", SqlDbType.Int) };
            commandParameters[0].Value = moduleId;
            DataSet set = SqlHelper.ExecuteDataset(connn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "MyTable";
            return set.Tables[0];
        }

        public void SetMytable(SqlConnection connn, string userId, string mytableLeft, string mytableRight)
        {
            string commandText = "UPDATE [dbo].[Users] SET [MytableLeft] = @MytableLeft, [MytableRight] = @MytableRight WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@MytableLeft", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = mytableLeft;
            commandParameters[2] = new SqlParameter("@MytableRight", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = mytableRight;
            SqlHelper.ExecuteNonQuery(connn, CommandType.Text, commandText, commandParameters);
        }

        public bool Update(SqlConnection connn, int moduleId, string modulePos, string moduleNo, int viewType)
        {
            string commandText = "UPDATE [dbo].[MyTable] SET [ModulePos] = @ModulePos, [ModuleNo] = @ModuleNo, [ViewType] = @ViewType WHERE [ModuleId] = @ModuleId";
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@ModuleId", SqlDbType.Int);
            commandParameters[0].Value = moduleId;
            commandParameters[1] = new SqlParameter("@ModulePos", SqlDbType.Char, 1);
            commandParameters[1].Value = modulePos;
            commandParameters[2] = new SqlParameter("@ModuleNo", SqlDbType.NVarChar, 10);
            commandParameters[2].Value = moduleNo;
            commandParameters[3] = new SqlParameter("@ViewType", SqlDbType.Int);
            commandParameters[3].Value = viewType;
            SqlHelper.ExecuteNonQuery(connn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

